package com.yhp.dao.impl;

import com.yhp.bean.Menu;
import com.yhp.bean.Role;
import com.yhp.bean.Users;
import com.yhp.dao.GradeDao;
import com.yhp.dao.UsersDao;
import com.yhp.util.Dbutils;

import javax.xml.registry.infomodel.User;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.AbstractList;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl extends Dbutils implements UsersDao {

    @Override
    public Users login(String name, String pass) {
        Users users = null;
        try {
            String sql = "SELECT * FROM users where loginname = ? and password = ?";
            ArrayList list = new ArrayList<>();
            list.add(name);
            list.add(pass);
            ResultSet query = query(sql, list);
            if(query == null){
                return null;
            }
            while (query.next()){
                users = new Users();
                users.setLoginName(query.getString("loginname"));
                users.setUserId(query.getInt("userid"));
                users.setRealName(query.getString("realname"));
                users.setRoleId(query.getInt("roleid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return users;
    }


    @Override
    public List<Menu> menuList(int id) {
        System.out.println(id);
        List list = null;
        try {
            String sql = "SELECT * FROM role r,middle l,menu m where r.roleid=l.roleid and l.menuid=m.menuid and r.roleid=?";
            List param = new ArrayList();
            param.add(id);
            ResultSet query = query(sql, param);
            list = new ArrayList();
            while (query.next()){
                Menu menu = new Menu();
                menu.setMenuId(query.getInt("menuid"));
                System.out.println(query.getInt("menuid"));
                menu.setUpMenuId(query.getInt("upmenuid"));
                System.out.println(query.getString("menuname"));
                menu.setMenuName(query.getString("menuname"));
                menu.setUrl(query.getString("url"));
                list.add(menu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return list;
    }

    @Override
    public List<Users> GetUsers(int pageIndex, int pageSize) {

        ArrayList<Users> list = null;
        try {
            StringBuffer sql =new StringBuffer( "SELECT * FROM users u,role r where u.roleid=r.roleid ");
            sql.append(" limit ?,?");
            ArrayList param = new ArrayList();
            param.add((pageIndex-1)*pageSize);
            param.add(pageSize);
            ResultSet query = query(sql.toString(), param);
            list = new ArrayList<>();
            while (query.next()){
                Users users = new Users();
                users.setUserId(query.getInt("userid"));
                users.setLoginName(query.getString("loginname"));
                users.setRealName(query.getString("realname"));
                Role role = new Role();
                role.setRoleName(query.getString("rolename"));
                users.setRole(role);
                list.add(users);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }

        return list;
    }

    @Override
    public int total() {
        int num = 0;
        try {
            String sql = "SELECT count(*) FROM users u,role r where u.roleid=r.roleid ";
            ResultSet query = query(sql, null);
            while (query.next()){
               num = query.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return num;
    }

    @Override
    public int addUser(Users user) {
        int update = 0;
        try {
            String sql = "insert into users values(null,?,?,?,?,?,?,?,?,?,?)";
            List param = new ArrayList();
            param.add(user.getLoginName());
            param.add(user.getPassWord());
            param.add(user.getRealName());
            param.add(user.getSex());
            param.add(user.getEmail());
            param.add(user.getAddRess());
            param.add(user.getPhone());
            param.add(user.getCardid());
            param.add(user.getIndesc());
            param.add(user.getRoleId());
            update = update(sql, param);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return update;
    }

    @Override
    public Users getId(int id) {
        Users users = null;
        try {
            users = new Users();
            String sql = "SELECT * FROM users u,role r WHERE u.userid=? and u.roleid=r.roleid";
            List param = new ArrayList();
            param.add(id);
            ResultSet query = query(sql, param);
            while (query.next()){
                users.setUserId(query.getInt("userid"));
                users.setLoginName(query.getString("loginname"));
                users.setRealName(query.getString("realname"));
                users.setSex(query.getInt("sex"));
                users.setRoleId(query.getInt("roleid"));
                users.setEmail(query.getString("email"));
                users.setPhone(query.getString("phone"));
                users.setAddRess(query.getString("address"));
                users.setCardid(query.getString("cardid"));
                users.setIndesc(query.getString("indesc"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return users;
    }

    @Override
    public int upDate(Users users) {
        int update = 0;
        try {
            String sql = "update users set " +
                    "loginname=?,password=?,realname=?," +
                    "sex=?,email=?,address=?," +
                    "phone=?,cardid=?,indesc=?," +
                    "roleid=? where userid=?";
            List param = new ArrayList();
            param.add(users.getLoginName());
            param.add(users.getPassWord());
            param.add(users.getRealName());
            param.add(users.getSex());
            param.add(users.getEmail());
            param.add(users.getAddRess());
            param.add(users.getPhone());
            param.add(users.getCardid());
            param.add(users.getIndesc());
            param.add(users.getRoleId());
            param.add(users.getUserId());
            update = update(sql, param);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return update;
    }

    @Override
    public int delete(int id) {
        int update = 0;
        try {
            String sql = "delete from users where userid=?";
            List param = new ArrayList();
            param.add(id);
            update = update(sql, param);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return update;
    }
}
